﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Albums
{
    internal static class AlbumDAL
    {
        internal static AlbumEntity GetAlbumByReader(IDataReader dr)
        {
            AlbumEntity album = new AlbumEntity();

            album.AlbumID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            album.ObjectID = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
            album.ObjectType = dr.IsDBNull(2) ? 0 : dr.GetInt32(2);
            album.Name = dr.IsDBNull(3) ? null : dr.GetString(3);
            album.Created = Util.ConverDateTime(dr.GetValue(4));
            album.ImgUrl = dr.IsDBNull(5) ? "Null.jpg" : dr.GetString(5);

            return album;
        }

        internal static int GetAlbumCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectAlbum = @"SELECT COUNT([AlbumID]) FROM [Album]";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectAlbum, ref parameterlst);
            DbCommand SelectAlbumCommand = db.GetSqlStringCommand(SelectAlbum);
            DbHelper.PackDbCommandByDbParameters(SelectAlbumCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectAlbumCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<AlbumEntity> GetAlbumList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<AlbumEntity> albumlst = new List<AlbumEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectAlbum = @"
                    SELECT [AlbumID],[ObjectID],[ObjectType],[Name],[Created],dbo.F_GetPhotoUrl(AlbumID,3,0) AS ImgUrl
                    FROM (
                        SELECT [AlbumID],[ObjectID],[ObjectType],[Name],[Created], 
                            ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectAlbum += " " + sortField + " " + sortMode + ",";
            SelectAlbum += @" [Album].[AlbumID] Desc
                    ) AS rowNum 
                        FROM [Album] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectAlbum, ref parameterlst);
            SelectAlbum += @"
                    ) AS [Album] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectAlbumCommand = db.GetSqlStringCommand(SelectAlbum);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectAlbumCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectAlbumCommand))
            {
                while (dr.Read())
                {
                    albumlst.Add(GetAlbumByReader(dr));
                }
            }

            return albumlst;
        }

        internal static List<AlbumEntity> GetAlbumList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<AlbumEntity> albumlst = new List<AlbumEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectAlbum = @"
                    SELECT [AlbumID],[ObjectID],[ObjectType],[Name],[Created],dbo.F_GetPhotoUrl(AlbumID,3,0) AS ImgUrl
                    FROM [Album] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectAlbum, ref parameterlst);
            SelectAlbum += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectAlbum += " " + sortField + " " + sortMode + ",";
            SelectAlbum += " [Album].[AlbumID] Desc ";

            DbCommand SelectAlbumCommand = db.GetSqlStringCommand(SelectAlbum);
            DbHelper.PackDbCommandByDbParameters(SelectAlbumCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectAlbumCommand))
            {
                while (dr.Read())
                {
                    albumlst.Add(GetAlbumByReader(dr));
                }
            }

            return albumlst;
        }

        internal static int GetShopAlbumCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectAlbum = @"SELECT COUNT(Album.[AlbumID]) FROM [Album] LEFT OUTER JOIN Shop ON Album.ObjectID = Shop.ShopID AND Album.ObjectType=1 ";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectAlbum, ref parameterlst);
            DbCommand SelectAlbumCommand = db.GetSqlStringCommand(SelectAlbum);
            DbHelper.PackDbCommandByDbParameters(SelectAlbumCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectAlbumCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<AlbumEntity> GetShopAlbumList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<AlbumEntity> albumlst = new List<AlbumEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectAlbum = @"
                    SELECT [AlbumID],[ObjectID],[ObjectType],[Name],[Created],dbo.F_GetPhotoUrl(AlbumID,3,0) AS ImgUrl
                    FROM (
                        SELECT [Album].[AlbumID],[Album].[ObjectID],[Album].[ObjectType],[Album].[Name],[Album].[Created], 
                            ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectAlbum += " " + sortField + " " + sortMode + ",";
            SelectAlbum += @" [Album].[AlbumID] Desc
                    ) AS rowNum 
                        FROM [Album] LEFT OUTER JOIN Shop ON Album.ObjectID = Shop.ShopID AND Album.ObjectType=1 ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectAlbum, ref parameterlst);
            SelectAlbum += @"
                    ) AS [Album] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectAlbumCommand = db.GetSqlStringCommand(SelectAlbum);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectAlbumCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectAlbumCommand))
            {
                while (dr.Read())
                {
                    albumlst.Add(GetAlbumByReader(dr));
                }
            }

            return albumlst;
        }

        internal static List<AlbumEntity> GetShopAlbumList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<AlbumEntity> albumlst = new List<AlbumEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectAlbum = @"
                    SELECT [Album].[AlbumID],[Album].[ObjectID],[Album].[ObjectType],[Album].[Name],[Album].[Created],
                        dbo.F_GetPhotoUrl(AlbumID,3,0) AS ImgUrl
                    FROM [Album] LEFT OUTER JOIN Shop ON Album.ObjectID = Shop.ShopID AND Album.ObjectType=1 ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectAlbum, ref parameterlst);
            SelectAlbum += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectAlbum += " " + sortField + " " + sortMode + ",";
            SelectAlbum += " [Album].[AlbumID] Desc ";

            DbCommand SelectAlbumCommand = db.GetSqlStringCommand(SelectAlbum);
            DbHelper.PackDbCommandByDbParameters(SelectAlbumCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectAlbumCommand))
            {
                while (dr.Read())
                {
                    albumlst.Add(GetAlbumByReader(dr));
                }
            }

            return albumlst;
        }

        internal static AlbumEntity GetAlbum(int albumID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            AlbumEntity album = null;

            string SelectAlbum = @"
                    SELECT [AlbumID],[ObjectID],[ObjectType],[Name],[Created],dbo.F_GetPhotoUrl(AlbumID,3,0) AS ImgUrl
                    FROM [Album] 
                    WHERE [Album].[AlbumID]=@AlbumID
                ";

            DbCommand SelectAlbumCommand = db.GetSqlStringCommand(SelectAlbum);
            db.AddInParameter(SelectAlbumCommand, "@AlbumID", DbType.Int32, albumID);

            using (IDataReader dr = db.ExecuteReader(SelectAlbumCommand))
            {
                if (dr.Read())
                {
                    album = GetAlbumByReader(dr);
                }
            }

            return album;
        }

        internal static int InsertAlbum(AlbumEntity album)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder InsertAlbum = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            InsertAlbum.Append(@"Declare @AlbumID int;
                    INSERT INTO [Album] 
				([ObjectID],[ObjectType],[Name],[Created])
				 VALUES (@ObjectID,@ObjectType,@Name,@Created);
                    SELECT @AlbumID=@@IDENTITY;
                ");

            parameterlst.Add(DbHelper.PackInParameter(db, "@ObjectID", DbType.Int32, album.ObjectID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ObjectType", DbType.Int32, album.ObjectType));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Name", DbType.String, album.Name));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, album.Created));

            #region 插入图片
            string idn = string.Empty;
            for (int n = 0; n < album.ImgUrllst.Count; n++)
            {
                idn = "_Photo" + n;
                InsertAlbum.Append("INSERT INTO [Photo] ([ObjectID],[ObjectType],[ImgUrl]) VALUES (@AlbumID, 3, @ImgUrl").Append(idn).Append(");");
                parameterlst.Add(DbHelper.PackInParameter(db, "@ImgUrl" + idn, DbType.String, album.ImgUrllst[n]));
            }
            #endregion

            InsertAlbum.Append("SELECT @AlbumID;");

            DbCommand InsertAlbumCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_InsertAlbum", InsertAlbum.ToString()));
            DbHelper.PackDbCommandByDbParameters(InsertAlbumCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(InsertAlbumCommand);

            return obj == null ? 0 : (int)obj;
        }

        internal static int UpdateAlbum(AlbumEntity album)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder UpdateAlbum = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            UpdateAlbum.Append(@"UPDATE [Album] 
				 SET [ObjectID]=@ObjectID,[ObjectType]=@ObjectType,[Name]=@Name,[Created]=@Created 
				 WHERE [AlbumID] = @AlbumID;");

            parameterlst.Add(DbHelper.PackInParameter(db, "@AlbumID", DbType.Int32, album.AlbumID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ObjectID", DbType.Int32, album.ObjectID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ObjectType", DbType.Int32, album.ObjectType));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Name", DbType.String, album.Name));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, album.Created));

            #region 更新图片
            string idn = string.Empty;
            UpdateAlbum.Append("DELETE FROM [Photo] WHERE [ObjectID]=@AlbumID AND [ObjectType]=3;");
            for (int n = 0; n < album.ImgUrllst.Count; n++)
            {
                idn = "_Photo" + n;
                UpdateAlbum.Append("INSERT INTO [Photo] ([ObjectID],[ObjectType],[ImgUrl]) VALUES (@AlbumID, 3, @ImgUrl").Append(idn).Append(");");
                parameterlst.Add(DbHelper.PackInParameter(db, "@ImgUrl" + idn, DbType.String, album.ImgUrllst[n]));
            }
            #endregion


            DbCommand UpdateAlbumCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_UpdateAlbum", UpdateAlbum.ToString()));
            DbHelper.PackDbCommandByDbParameters(UpdateAlbumCommand, parameterlst.ToArray());

            result = db.ExecuteNonQuery(UpdateAlbumCommand);

            return result;
        }

        internal static int DeleteAlbum(int albumID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteAlbum = @"
                DELETE FROM [Album] WHERE AlbumID=@AlbumID;
                DELETE FROM [Photo] WHERE ObjectID=@AlbumID AND ObjectType=3;";

            DbCommand DeleteAlbumCommand = db.GetSqlStringCommand(DeleteAlbum);
            db.AddInParameter(DeleteAlbumCommand, "@AlbumID", DbType.Int32, albumID);
            result = db.ExecuteNonQuery(DeleteAlbumCommand);

            return result;
        }

        internal static int SetAlbumEnable(int albumID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetAlbum = @"UPDATE [Album]
                    SET [IsEnable] = ([IsEnable]+1)%2
                    WHERE [AlbumID] = @AlbumID";

            DbCommand SetAlbumCommand = db.GetSqlStringCommand(SetAlbum);
            db.AddInParameter(SetAlbumCommand, "@AlbumID", DbType.Int32, albumID);
            result = db.ExecuteNonQuery(SetAlbumCommand);

            return result;
        }

        internal static int HitAlbum(int albumID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetAlbum = @"UPDATE [Album]
                    SET [Hits] = [Hits]+1
                    WHERE [AlbumID] = @AlbumID";

            DbCommand SetAlbumCommand = db.GetSqlStringCommand(SetAlbum);
            db.AddInParameter(SetAlbumCommand, "@AlbumID", DbType.Int32, albumID);
            result = db.ExecuteNonQuery(SetAlbumCommand);

            return result;
        }
    }
}
